create external table jms_dws.dws_s01_whole_operations_waybill_dt (
waybill_no                                              string            comment  '运单号'
,waybill_first_scantime                                 timestamp         comment  '最早操作时间'
,waybill_first_scantype                                 string            comment  '最早扫描类型'
,waybill_end_scantime                                   timestamp         comment  '最晚操作时间'
,waybill_end_scantype                                   string            comment  '最晚操作类型'
,all_operation_times                                    smallint          comment  '运单扫描总次数'
,center_operation_times                                 smallint          comment  '中心扫描总次数'
,entrepot_operation_times                               smallint          comment  '集散网点扫描次数'
,all_pass_netowrk_times                                 smallint          comment  '运单总经过网点数'
,pass_center_times                                      smallint          comment  '运单总经过中心数'
,pass_entrepot_times                                    smallint          comment  '运单总经过集散网点数'
,actual_taking_scantime                                 timestamp         comment  '实际揽收时间'
,actual_taking_scansitecode                             string            comment  '实际揽收网点'
,actual_taking_weight                                   decimal(10,2)     comment  '实际揽收重量'
,actual_taking_scantype                                 string            comment  '实际揽收扫描类型'
,taking_later_scantime                                  timestamp         comment  '最晚揽收时间'
,taking_early_scantime                                  timestamp         comment  '最早揽收时间'
,taking_early_scansitecode                              string            comment  '最早揽收网点'
,taking_early_weight                                    decimal(10,2)     comment  '最早揽收重量'
,warehousing_later_scantime                             timestamp         comment  '最晚入仓时间'
,warehousing_early_scantime                             timestamp         comment  '最早入仓时间'
,warehousing_early_scansitecode                         string            comment  '最早入仓网点'
,warehousing_early_weight                               decimal(10,2)     comment  '最早入仓重量'
,bagging_early_scantime                                 timestamp         comment  '最晚装袋时间'
,bagging_later_scantime                                 timestamp         comment  '最早装袋时间'
,bagging_later_scansitecode                             string            comment  '最早装袋网点'
,bagging_later_package_code                             string            comment  '最早装袋重量'

,first_network_network_code                             string            comment  '始发网点网点编码'

,first_network_loading_later_time                       timestamp         comment  '始发网点最晚装车时间'
,first_network_loading_later_weight                     decimal(10,2)     comment  '始发网点最晚装车扫描对应重量'

,first_network_send_later_time                          timestamp         comment  '始发网点最晚发车时间'
,first_network_send_later_weight                        decimal(10,2)     comment  '始发网点最晚发车扫描对应重量'

,first_network_loading_send_later_transfercode          string            comment  '始发网点最晚装车发件扫描对应任务号'
,first_network_loading_send_planned_departure_time      timestamp         comment  '始发网点最晚装车发件规划发车时间'
,first_network_loading_send_planned_arrival_time        timestamp         comment  '始发网点最晚装车发件规划到车时间'
,first_network_loading_send_actual_departure_time       timestamp         comment  '始发网点最晚装车发件实际发车时间'
,first_network_loading_send_actual_arrival_time         timestamp         comment  '始发网点最晚装车发件实际到车时间'
,first_network_loading_send_later_next_network_code     string            comment  '始发网点最晚装车发件下一站网点编码'

,first_nodal_network_code                               string            comment  '始发集散网点'

,first_nodal_unloading_early_time                       timestamp         comment  '始发集散最早卸车时间'
,first_nodal_unloading_early_weight                     decimal(10,2)     comment  '始发集散最早卸车扫描对应重量'

,first_nodal_arrival_early_time                         timestamp         comment  '始发集散最早到件时间'
,first_nodal_arrival_early_weight                       decimal(10,2)     comment  '始发集散最早到件扫描对应重量'

,first_nodal_unloading_arrival_early_transfercode       string            comment  '始发集散最早卸车到件扫描对应任务号'
,first_nodal_unloading_arrival_planned_departure_time   timestamp         comment  '始发集散最早卸车到件规划发车时间'
,first_nodal_unloading_arrival_planned_arrival_time     timestamp         comment  '始发集散最早卸车到件规划到车时间'
,first_nodal_unloading_arrival_actual_departure_time    timestamp         comment  '始发集散最早卸车到件实际发车时间'
,first_nodal_unloading_arrival_actual_arrival_time      timestamp         comment  '始发集散最早卸车到件实际到车时间'

,first_nodal_loading_later_time                         timestamp         comment  '始发集散最晚装车时间'
,first_nodal_loading_later_weight                       decimal(10,2)     comment  '始发集散最晚装车扫描对应重量'

,first_nodal_send_later_time                            timestamp         comment  '始发集散最晚发车时间'
,first_nodal_send_later_weight                          decimal(10,2)     comment  '始发集散最晚发车扫描对应重量'
,first_nodal_loading_send_later_transfercode            string            comment  '始发集散最晚装车发件扫描对应任务号'
,first_nodal_loading_send_planned_departure_time        timestamp         comment  '始发集散最晚装车发件规划发车时间'
,first_nodal_loading_send_planned_arrival_time          timestamp         comment  '始发集散最晚装车发件规划到车时间'
,first_nodal_loading_send_actual_departure_time         timestamp         comment  '始发集散最晚装车发件实际发车时间'
,first_nodal_loading_send_actual_arrival_time           timestamp         comment  '始发集散最晚装车发件实际到车时间'
,first_nodal_loading_send_later_next_network_code       string            comment  '始发集散最晚装车发件下一站网点编码'

,first_center_network_code                              string            comment  '始发转运中心'

,first_center_unloading_arrival_early_time              timestamp         comment  '始发中心最早卸车到件时间'
,first_center_unloading_arrival_early_weight            decimal(10,2)     comment  '始发中心最早卸车到件扫描对应重量'

,first_center_arrival_early_time                        timestamp         comment  '始发中心最早到件时间'
,first_center_arrival_early_weight                      decimal(10,2)     comment  '始发中心最早到件扫描对应重量'

,first_center_unloading_arrival_early_transfercode      string            comment  '始发中心最早卸车到件扫描对应任务号'
,first_center_unloading_arrival_planned_departure_time  timestamp         comment  '始发中心最早卸车到件规划发车时间'
,first_center_unloading_arrival_planned_arrival_time    timestamp         comment  '始发中心最早卸车到件规划到车时间'
,first_center_unloading_arrival_actual_departure_time   timestamp         comment  '始发中心最早卸车到件实际发车时间'
,first_center_unloading_arrival_actual_arrival_time     timestamp         comment  '始发中心最早卸车到件实际到车时间'

,first_center_loading_later_time                        timestamp         comment  '始发中心最晚装车时间'
,first_center_loading_later_weight                      decimal(10,2)     comment  '始发中心最晚装车扫描对应重量'

,first_center_loading_send_later_time                   timestamp         comment  '始发中心最晚装车发件时间'
,first_center_loading_send_later_weight                 decimal(10,2)     comment  '始发中心最晚装车发件扫描对应重量'

,first_center_loading_send_later_transfercode           string            comment  '始发中心最晚装车发件扫描对应任务号'
,first_center_loading_send_planned_departure_time       timestamp         comment  '始发中心最晚装车发件规划发车时间'
,first_center_loading_send_planned_arrival_time         timestamp         comment  '始发中心最晚装车发件规划到车时间'
,first_center_loading_send_actual_departure_time        timestamp         comment  '始发中心最晚装车发件实际发车时间'
,first_center_loading_send_actual_arrival_time          timestamp         comment  '始发中心最晚装车发件实际到车时间'
,first_center_loading_send_later_next_network_code      string            comment  '始发中心最晚装车发件下一站网点编码'

,end_center_network_code                                string            comment  '末端转运中心'
,end_center_unloading_early_time                        timestamp         comment  '末端中心最早卸车时间'
,end_center_unloading_early_weight                      decimal(10,2)     comment  '末端中心最早卸车扫描对应重量'
,end_center_arrival_early_time                          timestamp         comment  '末端中心最早到件时间'
,end_center_arrival_early_weight                        decimal(10,2)     comment  '末端中心最早到件扫描对应重量'
,end_center_unloading_arrival_early_transfercode        string            comment  '末端中心最早卸车到件扫描对应任务号'
,end_center_unloading_arrival_planned_departure_time    timestamp         comment  '末端中心最早卸车到件规划发车时间'
,end_center_unloading_arrival_planned_arrival_time      timestamp         comment  '末端中心最早卸车到件规划到车时间'
,end_center_unloading_arrival_actual_departure_time     timestamp         comment  '末端中心最早卸车到件实际发车时间'
,end_center_unloading_arrival_actual_arrival_time       timestamp         comment  '末端中心最早卸车到件实际到车时间'
,end_center_loading_later_time                          timestamp         comment  '末端中心最晚装车时间'
,end_center_loading_later_weight                        decimal(10,2)     comment  '末端中心最晚装车扫描对应重量'
,end_center_send_later_time                             timestamp         comment  '末端中心最晚发件时间'
,end_center_send_later_weight                           decimal(10,2)     comment  '末端中心最晚发件扫描对应重量'
,end_center_loading_send_later_transfercode             string            comment  '末端中心最晚装车发件扫描对应任务号'
,end_center_loading_send_planned_departure_time         timestamp         comment  '末端中心最晚装车发件规划发车时间'
,end_center_loading_send_planned_arrival_time           timestamp         comment  '末端中心最晚装车发件规划到车时间'
,end_center_loading_send_actual_departure_time          timestamp         comment  '末端中心最晚装车发件实际发车时间'
,end_center_loading_send_actual_arrival_time            timestamp         comment  '末端中心最晚装车发件实际到车时间'
,end_center_loading_send_later_next_network_code        string            comment  '末端中心最晚装车发件下一站网点编码'

,end_nodal_network_code                                 string            comment  '目的集散网点'
,end_nodal_unloading_early_time                         timestamp         comment  '目的集散最早卸车时间'
,end_nodal_unloading_early_weight                       decimal(10,2)     comment  '目的集散最早卸车扫描对应重量'

,end_nodal_arrival_early_time                           timestamp         comment  '目的集散最早到件时间'
,end_nodal_arrival_early_weight                         decimal(10,2)     comment  '目的集散最早到件扫描对应重量'

,end_nodal_unloading_arrival_early_transfercode         string            comment  '目的集散最早卸车到件扫描对应任务号'
,end_nodal_unloading_arrival_planned_departure_time     timestamp         comment  '目的集散最早卸车到件规划发车时间'
,end_nodal_unloading_arrival_planned_arrival_time       timestamp         comment  '目的集散最早卸车到件规划到车时间'
,end_nodal_unloading_arrival_actual_departure_time      timestamp         comment  '目的集散最早卸车到件实际发车时间'
,end_nodal_unloading_arrival_actual_arrival_time        timestamp         comment  '目的集散最早卸车到件实际到车时间'

,end_nodal_loading_later_time                           timestamp         comment  '目的集散最晚装车时间'
,end_nodal_loading_later_weight                         decimal(10,2)     comment  '目的集散最晚装车扫描对应重量'

,end_nodal_send_later_time                              timestamp         comment  '目的集散最晚发车时间'
,end_nodal_send_later_weight                            decimal(10,2)     comment  '目的集散最晚发车扫描对应重量'
,end_nodal_loading_send_later_transfercode              string            comment  '目的集散最晚装车发件扫描对应任务号'
,end_nodal_loading_send_planned_departure_time          timestamp         comment  '目的集散最晚装车发件规划发车时间'
,end_nodal_loading_send_planned_arrival_time            timestamp         comment  '目的集散最晚装车发件规划到车时间'
,end_nodal_loading_send_actual_departure_time           timestamp         comment  '目的集散最晚装车发件实际发车时间'
,end_nodal_loading_send_actual_arrival_time             timestamp         comment  '目的集散最晚装车发件实际到车时间'
,end_nodal_loading_send_later_next_network_code         string            comment  '目的集散最晚装车发件下一站网点编码'

,deliver_early_scantime                                 timestamp         comment  '最早派件时间'
,deliver_later_scantime                                 timestamp         comment  '最晚派件时间'
,deliver_later_scansitecode                             string            comment  '最晚派件网点'
,deliver_later_weight                                   decimal(10,2)     comment  '最晚派件重量'
,sign_later_scantime                                    timestamp         comment  '最晚签收时间'
,sign_early_scantime                                    timestamp         comment  '最早签收时间'
,sign_early_scansitecode                                string            comment  '最早签收网点'
,sign_early_weight                                      decimal(10,2)     comment  '最早签收重量'
,aging_sign_scantime                                    timestamp         comment  '实效签收时间'
,aging_sign_scansitecode                                string            comment  '实效签收网点'
,aging_sign_weight                                      decimal(10,2)     comment  '实效签收重量'
,aging_sign_scantype                                    string            comment  '实效签收扫描类型'
,actual_sign_scantime                                   timestamp         comment  '实际签收时间'
,actual_sign_scansitecode                               string            comment  '实际签收网点'
,actual_sign_weight                                     decimal(10,2)     comment  '实际签收重量'
,actual_sign_scantype                                   string            comment  '实际签收扫描类型'

,first_code                                             string            comment  '一段码(运单表获取)'
,second_code                                            string            comment  '二段码(运单表获取)'
,third_code                                             string            comment  '三段码(运单表获取)'
,is_sign                                                smallint          comment  '签收标识(运单表获取)'
,waybill_source_code                                    string            comment  '运单来源code(运单表获取)'
,waybill_source_name                                    string            comment  '运单来源code(运单表获取)'
,is_void                                                smallint          comment  '是否作废件,1是，0否(运单表获取)'
,is_refund                                              smallint          comment  '是否退件,1是，0否(运单表获取)'
,pick_network_code                                      string            comment  '寄件网点code(运单表获取)'
,dispatch_network_code                                  string            comment  '派件网点code(运单表获取)'
,input_network_code                                     string            comment  '录入网点code(运单表获取)'
,order_source_code                                      string            comment  '订单来源编码(运单表获取)'
,is_delete                                              smallint          comment  '是否删除,1未删除，2已删除(运单表获取)'
,goods_name                                             string            comment  '物品名称(运单表获取)'
,is_abnormal                                            smallint          comment  '问题件标识,1是，0否(运单表获取)'
,waybill_weight                                         decimal(10,2)     comment  '运单重量,单位千克(运单表获取)'
,settlement_weight                                      decimal(10,2)     comment  '结算重量(运单表获取)'
,aim_center_code                                        string            comment  '目的转运中心(通过一段码关联一段码表获取)'
)
    comment  '运单维度宽表，统计近15天'
    partitioned by (dt string comment '时间分区')
    stored as parquet
    location '/dw/hive/jms_dws.db/external/dws_s01_whole_operations_waybill_dt'
    tblproperties (
      'discover.partitions'= 'false',
      'parquet.column.index.access'= 'true'
        );
